package com.zyd.shiro.persistence.dao;

import javafx.scene.chart.ValueAxis;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.zyd.shiro.persistence.beans.TrainMeeting;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * 数据访问接口
 * @author Administrator
 *
 */
public interface MallTrainMeetingDao extends JpaRepository<TrainMeeting,Integer>,JpaSpecificationExecutor<TrainMeeting>{

    /**
     * 查找所有有效会议培训
     * @param nowDate
     * @param type
     * @return
     */
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_date>= ?1 and bean.meeting_type=?2 ORDER BY meeting_date DESC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_dateAfterDESC(Date nowDate,int type);
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_date>= ?1 and bean.meeting_type=?2 ORDER BY meeting_date ASC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_dateAfterASC(Date nowDate,int type);

    @Query(value = "select * from mall_train_meeting bean where  bean.meeting_type=?1 ORDER BY meeting_date DESC",nativeQuery = true)
    List<TrainMeeting> findAllDESC(int type);

    /**
     * 按照主题查找有效会议培训
     * @param theme
     * @param date
     * @param type
     * @return
     */
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_theme = ?1 and bean.meeting_date>= ?2 and bean.meeting_type=?3 ORDER BY meeting_date ASC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_themeEqualsASC(String theme,Date date,int type);
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_theme = ?1 and bean.meeting_date>= ?2 and bean.meeting_type=?3 ORDER BY meeting_date DESC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_themeEqualsDESC(String theme,Date date,int type);

    /**
     * 按照地点查找有效会议培训
     * @param place
     * @param date
     * @param type
     * @return
     */

    @Query(value = "select * from mall_train_meeting bean where bean.meeting_place = ?1  and bean.meeting_date>= ?2 and bean.meeting_type=?3 ORDER BY meeting_date ASC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_placeEqualsASC(String place,Date date,int type);
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_place = ?1 and bean.meeting_date>= ?2 and bean.meeting_type=?3 ORDER BY meeting_date DESC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_placeEqualsDESC(String place,Date date,int type);


    /**
     * 按照标题查找会议培训
     * @param title
     * @return
     */
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_title LIKE CONCAT('%',?1,'%')  and bean.meeting_type=?2 ORDER BY meeting_date ASC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_titleLikeASC(String title,int type);
    @Query(value = "select * from mall_train_meeting bean where bean.meeting_title LIKE CONCAT('%',?1,'%')  and bean.meeting_type=?2 ORDER BY meeting_date DESC",nativeQuery = true)
    List<TrainMeeting> findAllByMeeting_titleLikeDESC(String title,int type);


    /**主题
     *
     */
    @Query(value = "select count(*) from mall_theme bean where bean.theme_name = ?1",nativeQuery = true)
    int IshaveTheme(String theme);
    @Query(value = "insert into mall_theme(theme_name) value(?1)",nativeQuery = true)
    void addTheme(String theme);

    @Query(value = "select theme_name from mall_theme",nativeQuery = true)
    List<String> findAllTheme();
}
